﻿Imports System.Data.Odbc
Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Web
Imports System.Net
Imports System.Text
Imports System.IO
Imports System.Security.Cryptography.X509Certificates
Imports System.Net.Security
Imports System.Web.SessionState
Imports System.Collections
Imports System.Reflection
Imports System.Web.Script.Serialization
Imports Newtonsoft.Json
Imports Parse

Namespace ASP

    <AttributeUsage(AttributeTargets.All)>
    Public Class NeedAuth
        Inherits Attribute

    End Class
    <AttributeUsage(AttributeTargets.All)>
    Public Class HideData
        Inherits Attribute

    End Class
    <AttributeUsage(AttributeTargets.All)>
    Public Class FunctionState
        Inherits Attribute

        Public Enum FState
            Test
            Bug
            Stable
        End Enum

        Public State As FState

        Public Sub New(FS As FState)
            State = FS
        End Sub

    End Class
    <AttributeUsage(AttributeTargets.All)>
    Public Class Description
        Inherits Attribute

        Public Description As String = ""

        Sub New(dataString As String)

            Description = dataString

        End Sub

    End Class
    <AttributeUsage(AttributeTargets.All)>
    Public Class PostParametrData
        Inherits Attribute

        Public Parametrs As New Dictionary(Of String, Type)

        Sub New(paramsName As String(), prms_types As Type())


            For i As Integer = 0 To paramsName.Length - 1

                Parametrs.Add(paramsName(i), prms_types(i))

            Next

        End Sub

    End Class
    <AttributeUsage(AttributeTargets.All)>
    Public Class PostParametrInfo
        Inherits Attribute

        Public Parametrs As String = ""

        Sub New(data As String)

            Parametrs = data

        End Sub

    End Class

    Public Class SessionItem

        Public Data As New Dictionary(Of String, String)

    End Class
    Public Class WSession

        Public Data As New Dictionary(Of String, String)
        Public WID As String

        Sub Push(key As String, d As String)

            If Data.ContainsKey(key) = True Then Data(key) = d Else Data.Add(key, d)

            SaveChanes()

        End Sub

        Sub New()

        End Sub

        Sub New(context As HttpContext)

            Dim _context As HttpContext = HttpContext.Current

            Dim files() As String = Directory.GetFiles(HttpContext.Current.Server.MapPath("/Session/"))

            For i As Integer = 0 To files.Length - 1

                Dim fInfo As New FileInfo(files(i))

                If fInfo.LastWriteTimeUtc.AddMinutes(90) > Now Then

                End If

            Next

            Dim currentCookie As HttpCookie
            If IsNothing(_context.Request.Cookies) = False Then

                If _context.Request.Cookies.Count <> 0 Then


                    For i As Integer = 0 To _context.Request.Cookies.AllKeys.Length - 1

                        If _context.Request.Cookies.AllKeys(i) = "WRAD_SESSION" Then

                            currentCookie = _context.Request.Cookies(i)

                        End If

                    Next
                End If

            End If

            If IsNothing(_context.Response.Cookies) = False Then

                If _context.Response.Cookies.Count <> 0 Then


                    For i As Integer = 0 To _context.Response.Cookies.AllKeys.Length - 1

                        If _context.Response.Cookies.AllKeys(i) = "WRAD_SESSION" Then

                            currentCookie = _context.Response.Cookies(i)

                        End If

                    Next
                End If

            End If

            If IsNothing(currentCookie) = True Then

                currentCookie = New HttpCookie("WRAD_SESSION", Guid.NewGuid.ToString)

                _context.Request.Cookies.Add(currentCookie)

            End If


            WID = currentCookie.Value

            If WID = "" Then

                WID = Guid.NewGuid.ToString

            End If
            _context.Response.AppendCookie(currentCookie)


            If File.Exists(_context.Server.MapPath("/Session/" & currentCookie.Value)) = False Then

                Data = New SessionItem().Data

                File.WriteAllText(_context.Server.MapPath("/Session/" & currentCookie.Value), JsonConvert.SerializeObject(Me))

            Else
                Try

                    Data = JsonConvert.DeserializeObject(Of WSession)(File.ReadAllText(_context.Server.MapPath("/Session/" & currentCookie.Value))).Data
                    WID = JsonConvert.DeserializeObject(Of WSession)(File.ReadAllText(_context.Server.MapPath("/Session/" & currentCookie.Value))).WID

                Catch ex As Exception

                    Data = New SessionItem().Data

                    File.WriteAllText(_context.Server.MapPath("/Session/" & currentCookie.Value), JsonConvert.SerializeObject(Me))

                End Try

            End If

        End Sub

        Public Sub SaveChanes()

            Dim context As HttpContext = HttpContext.Current

            File.WriteAllText(context.Server.MapPath("/Session/" & WID), JsonConvert.SerializeObject(Me))

        End Sub

    End Class

    Public Class WRADA_PUSH_SERVICE


#Region "GCM"

        Public GCMapiKey As String

        Public ParseAPIAppKey As String

        Public ParseAPINetKey As String

        Public Sub InitializeParse()

            ParseClient.Initialize(ParseAPIAppKey, ParseAPINetKey)

        End Sub

        Public Sub New()
            Dim was As Boolean = False

            For i As Integer = 0 To ConfigurationManager.AppSettings.Count - 1
                If ConfigurationManager.AppSettings.AllKeys(i) = "GCMBrowserKey" Then was = True
            Next

            If was = False Then
                Throw New Exception("Need variable GCMBrowserKey in web.config")
            End If

            GCMapiKey = ConfigurationManager.AppSettings("GCMBrowserKey")

            was = False

            For i As Integer = 0 To ConfigurationManager.AppSettings.Count - 1
                If ConfigurationManager.AppSettings.AllKeys(i) = "ParseAPIAppKey" Then was = True
            Next

            If was = False Then
                Throw New Exception("Need variable ParseAPIAppKey in web.config")
            End If

            ParseAPIAppKey = ConfigurationManager.AppSettings("ParseAPIAppKey")

            was = False

            For i As Integer = 0 To ConfigurationManager.AppSettings.Count - 1
                If ConfigurationManager.AppSettings.AllKeys(i) = "ParseAPINetKey" Then was = True
            Next

            If was = False Then
                Throw New Exception("Need variable ParseAPINetKey in web.config")
            End If

            ParseAPINetKey = ConfigurationManager.AppSettings("ParseAPINetKey")

        End Sub

        Public Function SendGCMNotifications(deviceId As ArrayList, contentTitle As String, tickerText As String, message As String) As String
            Try

                For i As Integer = 0 To deviceId.Count - 1

                    SendGCMNotification(deviceId(i), contentTitle, tickerText, message)

                Next

                Return "OK"

            Catch ex As Exception

                Return "Error. " & ex.Message

            End Try

        End Function

        Public Function SendGCMNotification(deviceId As String, contentTitle As String, tickerText As String, message As String) As String


            Const postDataContentType As String = "application/json"

            Dim postData As String = String.Format("{{ ""registration_ids"": [ ""{0}"" ], " & """data"": {{""tickerText"":""{1}"", " & """contentTitle"":""{2}"", " & """message"": ""{3}""}}}}", deviceId, tickerText, contentTitle, message)
            ServicePointManager.ServerCertificateValidationCallback = Function(sender As Object,
                                                     certificate As X509Certificate,
                                                     chain As X509Chain,
                                                     sslPolicyErrors As SslPolicyErrors) As Boolean

                                                                          Return True
                                                                      End Function

            '//  MESSAGE CONTENT

            Dim byteArray As Byte() = Encoding.UTF8.GetBytes(postData)

            '//  CREATE REQUEST

            Dim request As HttpWebRequest = CType(WebRequest.Create("https://android.googleapis.com/gcm/send"), HttpWebRequest)
            request.Method = "POST"
            request.KeepAlive = False
            request.ContentType = postDataContentType
            request.Headers.Add("Authorization: key=" & GCMapiKey)
            request.ContentLength = byteArray.Length

            Dim dataStream As Stream = request.GetRequestStream()
            dataStream.Write(byteArray, 0, byteArray.Length)
            dataStream.Close()


            '//  SEND MESSAGE

            Try


                Dim response As WebResponse = request.GetResponse()
                Dim responseCode As HttpStatusCode = CType(response, HttpWebResponse).StatusCode
                If (responseCode.Equals(HttpStatusCode.Unauthorized) Or responseCode.Equals(HttpStatusCode.Forbidden)) Then

                    Return "Error. Unauthorized - need new token"

                ElseIf (Not responseCode.Equals(HttpStatusCode.OK)) Then

                    Return "Error.Response from web service isn't OK"

                End If

                Dim reader As StreamReader = New StreamReader(response.GetResponseStream())
                Dim responseLine As String = reader.ReadToEnd()
                reader.Close()

                Return responseLine

            Catch ex As Exception
                Return "Error. " & ex.Message
            End Try

        End Function

        Public Function ValidateServerCertificate(sender As Object,
                                                     certificate As X509Certificate,
                                                     chain As X509Chain,
                                                     sslPolicyErrors As SslPolicyErrors) As Boolean

            Return True
        End Function

#End Region

#Region "Parse"

        Public Function SendParseNotification(chanel As String, title As String, message As String, Optional order_id As String = "") As String

            Try

                Dim push As New ParsePush
                push.Data = New Dictionary(Of String, Object)
                push.Data.Add("sound", "default")
                push.Data.Add("title", title)
                push.Data.Add("alert", message)
                push.Data.Add("inAppMessage", order_id)
                push.Channels = {chanel.Replace(" ", "")}
                push.SendAsync()

                Return "OK"

            Catch ex As Exception
                Return "Error. " & ex.Message
            End Try

        End Function

#End Region


    End Class

    Public Class WRADA_HTTP_Module
        Implements IHttpModule, IRequiresSessionState

        Public ALL_TABLES As ArrayList



        Public Sub Dispose() Implements IHttpModule.Dispose
            If (IsNothing(HttpContext.Current) <> True) Then
                HttpContext.Current.Application.UnLock()
            End If
        End Sub

        Sub BeginRequest(sender As Object, e As EventArgs)

            HttpContext.Current.Application.Lock()

            Dim app As HttpApplication = CType(sender, HttpApplication)


            If app.Request.Url.AbsoluteUri.ToLower.Contains("/wrada/") Then

                'Получаем все параметры
                Dim parms() As String = Split(app.Request.Url.AbsolutePath.Replace("/wrada/", ""), "/")

                Dim pData As New Dictionary(Of String, Object)

                If parms.Length = 0 Or parms.Length = 1 Then

                    app.Response.Write("{""data"" : ""WRADA work""}")
                    app.Response.End()

                End If

                For i As Integer = 1 To parms.Length - 1 Step 2
                    If pData.ContainsKey(parms(i - 1)) = True Then

                        If pData.ContainsKey("method") = True Then

                            If pData("method") = "data" Then

                                Continue For

                            End If

                        End If

                    End If

                    pData.Add(parms(i - 1), parms(i))

                Next

                'Очищаем заголовки контент
                app.Context.Response.Clear()
                app.Context.Response.ClearHeaders()

                Dim session As WSession = New WSession(app.Context)


                If pData.ContainsKey("method") = False Then

                    pData.Add("method", "unknow")

                End If

                Select Case pData("method")
                    Case "assembly"
                        CreateAssemblies(app, False, session)
                    Case "call"
                        CallClassMethos(app, pData, session)
                    Case "var"
                        CallVariableAccess(app, pData)
                    Case "data"
                        CallDataBase(app, pData)
                    Case Else
                        app.Response.Write("{""error"" : ""CANT_RETURN_INFORMATION"", ""target"":""" & pData("method") & """}")
                End Select

                'GetContext().Dispose()

                HttpContext.Current.Application.UnLock()

                app.Context.Response.End()
            End If
        End Sub

        Public Sub Init(context As HttpApplication) Implements IHttpModule.Init
            AddHandler context.PreRequestHandlerExecute, AddressOf BeginRequest
            'AddHandler context.Error, AddressOf BeginRequest
        End Sub


        Public Sub CallVariableAccess(app As HttpApplication, parms As Dictionary(Of String, Object))

            If parms.ContainsKey("class") = False Then

                app.Response.Write("{""error"" : ""NEED_SELECT_CLASS""}")
                app.Response.End()

            End If

            If parms.ContainsKey("variable") = False Then

                app.Response.Write("{""error"" : ""NEED_SELECT_VARIABLE""}")
                app.Response.End()

            End If

            Dim moduleName As Type = Assembly.GetExecutingAssembly.GetTypes.First(Function(x) x.Name = parms("class"))

            If IsNothing(moduleName) = False Then

                Dim var As FieldInfo = moduleName.GetFields().Where(Function(x) x.Name = parms("variable")).First

                If IsNothing(var) = False Then

                    app.Response.Write("{""" & var.Name & """ : """ & var.GetValue(Me) & """}")
                    app.Response.End()

                Else

                    app.Response.Write("{""error"" : ""CANT_FIND_VARIABLE""}")
                    app.Response.End()

                End If

            Else

                app.Response.Write("{""error"" : ""CANT_FIND_CLASS""}")
                app.Response.End()

            End If

        End Sub

        Public Sub CallClassMethos(app As HttpApplication, parms As Dictionary(Of String, Object), Optional session As WSession = Nothing)

            Dim moduleName As Type = Assembly.GetExecutingAssembly.GetTypes.First(Function(x) x.Name = parms("class"))

            If IsNothing(moduleName) = False Then

                Dim method As MethodInfo = moduleName.GetMethods().Where(Function(x) x.Name = parms("func")).First

                If method.GetCustomAttributes(False).Length > 0 Then

                    If method.GetCustomAttributes(False).Count(Function(x) x.ToString().IndexOf("NeedAuth") >= 0) > 0 Then

                        If IsAuth(app, session) = False Then
                            'GetContext().Dispose()

                            HttpContext.Current.Application.UnLock()
                            app.Response.Write("{""error"" : ""NEED_AUTH"", ""message"":""You need login in WRAD""}")
                            app.Response.End()
                            Return
                        End If

                    End If

                End If

                If method.GetParameters.Length <> (parms.Count - 3) Then
                    app.Response.Write("{""error"" : ""WRONG_PARAMETERS_COUNT""}")
                Else
                    Dim jr As New JavaScriptSerializer
                    If parms.Count = 3 Then
                        Try
                            If method.ReturnType.Name = "Void" Then
                                method.Invoke(Me, Nothing)
                                If InStr(LCase(method.Name), "qiwi") = 0 Then app.Context.Response.Write("{""data"":""OK""}") Else method.Invoke(Me, Nothing)
                            Else
                                If InStr(LCase(method.Name), "qiwi") = 0 Then app.Context.Response.Write((jr.Serialize(method.Invoke(Me, Nothing)))) Else method.Invoke(Me, Nothing)
                            End If
                        Catch ex As Exception

                            If IsNothing(ex.InnerException) = False Then
                                If InStr(LCase(method.Name), "qiwi") = 0 Then app.Response.Write("{""error"" : ""EXECUTION_ERROR"", ""message"":""" & ex.InnerException.Message & " " & ex.InnerException.Source & """}")
                            Else
                                If InStr(LCase(method.Name), "qiwi") = 0 Then app.Response.Write("{""error"" : ""EXECUTION_ERROR"", ""message"":""" & ex.Message & """}")
                            End If

                        End Try
                    Else
                        Dim mparms(((parms.Count - 3)) - 1) As Object
                        Dim ii As Integer = 0
                        For Each parametr In parms.Where(Function(x) x.Key <> "method" And x.Key <> "class" And x.Key <> "func")
                            mparms(ii) = parametr.Value
                            ii += 1
                        Next
                        Try
                            If method.ReturnType.Name = "Void" Then

                                method.Invoke(Me, mparms)

                                If InStr(LCase(method.Name), "qiwi") = 0 Then app.Context.Response.Write("{""data"":""OK""}")
                            Else

                                If InStr(LCase(method.Name), "qiwi") = 0 Then app.Context.Response.Write((jr.Serialize(method.Invoke(Me, mparms)))) Else method.Invoke(Me, mparms)
                            End If
                        Catch ex As Exception
                            If InStr(LCase(method.Name), "qiwi") = 0 Then app.Response.Write("{""error"" : ""EXECUTION_ERROR_2"", ""message"":""" & ex.Message & """}")
                        End Try
                    End If
                End If
                If InStr(LCase(method.Name), "qiwi") = 0 Then app.Context.Response.End()
            End If
        End Sub

        Public Function IsAuth(app As HttpApplication, Optional session As WSession = Nothing) As Boolean

            If session.Data.ContainsKey("WRAD_AUTH") = False Then Return False

            If CBool(session.Data("WRAD_AUTH")) = True Then

                Return True

            End If

            Return False

        End Function

        Public Sub CallDataBase(app As HttpApplication, parms As Dictionary(Of String, Object))

            If parms.ContainsKey("table") = False Then
                app.Response.Write("{""error"" : ""NEED_SELECT_TABLE""}")
                app.Response.End()
            End If

            If parms.ContainsKey("select") = False Then
                app.Response.Write("{""error"" : ""NEED_SELECT_TABLE""}")
                app.Response.End()
            End If

            Dim tables() As String = parms("table").Split(",")

            Dim _tables As String = ""

            For i As Integer = 0 To tables.Length - 1

                If _tables <> "" Then _tables += ", "

                _tables += String.Format("'{0}'", tables(i))

            Next

            If parms("select") = "schema" Then

                Using con As New OdbcConnection(ConfigurationManager.AppSettings("WRADA_ConnectionString")), com As New OdbcCommand("SELECT `information_schema`.`COLUMNS`.`TABLE_NAME`, `information_schema`.`COLUMNS`.`COLUMN_NAME`, `information_schema`.`COLUMNS`.`DATA_TYPE` FROM `information_schema`.`COLUMNS`  WHERE `information_schema`.`COLUMNS`.`TABLE_SCHEMA`='" & "###" & "' AND `information_schema`.`COLUMNS`.`TABLE_NAME` IN (" & _tables & ");")

                    con.Open()
                    com.Connection = con
                    com.CommandText = com.CommandText.Replace("###", con.Database)

                    Dim schema As New Dictionary(Of String, Dictionary(Of String, Object))

                    Using reader As OdbcDataReader = com.ExecuteReader

                        While reader.Read

                            If schema.ContainsKey(reader.GetString(0)) = False Then
                                schema.Add(reader.GetString(0), New Dictionary(Of String, Object))
                            End If

                            schema(reader.GetString(0)).Add(reader.GetString(1), reader.GetString(2))

                        End While
                    End Using

                    com.CommandText = "SELECT TABLE_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM `information_schema`.`KEY_COLUMN_USAGE` WHERE TABLE_SCHEMA = '" & con.Database & "' AND (TABLE_NAME IN (" & _tables & ") OR REFERENCED_TABLE_NAME IN (" & _tables & ")) AND REFERENCED_TABLE_NAME  IS NOT NULL AND REFERENCED_COLUMN_NAME IS NOT NULL"

                    Dim foringsToProcess As New ArrayList

                    Using reader As OdbcDataReader = com.ExecuteReader

                        While reader.Read

                            Dim tbl As String = reader.GetString(0)
                            Dim rT As String = reader.GetString(1)
                            Dim rC As String = reader.GetString(2)

                            foringsToProcess.Add({tbl, rT, rC})

                        End While
                    End Using


                    For i As Integer = 0 To foringsToProcess.Count - 1

                        Dim foingschema As New Dictionary(Of String, String)

                        If schema.ContainsKey(foringsToProcess(i)(0)) = True Then

                            com.CommandText =
                                "SELECT `information_schema`.`COLUMNS`.`TABLE_NAME`, `information_schema`.`COLUMNS`.`COLUMN_NAME`, `information_schema`.`COLUMNS`.`DATA_TYPE` FROM `information_schema`.`COLUMNS`  WHERE `information_schema`.`COLUMNS`.`TABLE_SCHEMA`='" & con.Database & "' AND `information_schema`.`COLUMNS`.`TABLE_NAME` = '" & foringsToProcess(i)(1) & "';"
                        Else
                            com.CommandText = "SELECT `information_schema`.`COLUMNS`.`TABLE_NAME`, `information_schema`.`COLUMNS`.`COLUMN_NAME`, `information_schema`.`COLUMNS`.`DATA_TYPE` FROM `information_schema`.`COLUMNS`  WHERE `information_schema`.`COLUMNS`.`TABLE_SCHEMA`='" & con.Database & "' AND `information_schema`.`COLUMNS`.`TABLE_NAME` = '" & foringsToProcess(i)(0) & "';"
                        End If

                        Using reader As OdbcDataReader = com.ExecuteReader

                            While reader.Read
                                foingschema.Add(reader.GetString(1), reader.GetString(2))
                            End While
                        End Using

                        If schema.ContainsKey(foringsToProcess(i)(0)) = True Then
                            schema(foringsToProcess(i)(0)).Add(foringsToProcess(i)(1), foingschema)
                        Else
                            schema(foringsToProcess(i)(1)).Add(foringsToProcess(i)(0), {foingschema})
                        End If

                    Next

                    Dim js As New JavaScriptSerializer

                    app.Response.Write(js.Serialize(schema))
                End Using
            Else

                'Подготавливаем запрос в базу данных. Пересобираем передаваемые параметры

                Dim SQL_Parametrs As New ArrayList

                If app.Context.Request.Form.Count = 0 Then

                    Dim urlParams() As String = Split(app.Context.Request.Url.AbsolutePath.Replace("/wrada/", "").Replace("method/data/", ""), "/")

                    If urlParams.Length = 0 Then

                        SendExeption("NEED_SELECT_TABLE")

                    End If

                    For i As Integer = 0 To urlParams.Length - 1

                        SQL_Parametrs.Add(urlParams(i))

                    Next

                    If SQL_Parametrs.Contains("select") = False Or SQL_Parametrs.Contains("table") = False Then

                        SendExeption("NEED_SELECT_AND_TABLE")

                    End If

                    If IsNothing(ALL_TABLES) = True Then ALL_TABLES = GetTablesFromSchema()

                    Dim TotalSQL As String = ""

                    Dim _SQL_SELECT As String = ""
                    Dim _SQL_WHERE As String = ""
                    Dim _SQL_ORDER As String = ""
                    Dim _SQL_ATACH As String = ""
                    Dim _SQL_GROUP As String = ""
                    Dim _SQL_FROM As String = ""

                    Dim CuurentAttacjSql As New ArrayList

                    Dim CurrentTable As String = ""

                    Dim CurrentColumns As New ArrayList

                    Dim TableOrder As New ArrayList
                    Dim last_state = -1
                    Dim state As Integer = -1
                    'При attach и up нужно очищать _SQL переменные и заполнять TotalSQL

                    '-1 - none
                    '0 - wait for table
                    '1 - table was
                    '2 - wait for parametr
                    '3 - where was and
                    '4 - order was
                    '5 - group was
                    '6 - attach was, wait for tablename
                    '7 - wait for column
                    '8 - wait for operator
                    '9 - up was
                    '10 - wait for parameter
                    '11 - where was or
                    '12 - wait for value or column
                    '13 - from up
                    '14 - oder desc was

                    For i As Integer = 0 To SQL_Parametrs.Count - 1


                        Select Case state
                            Case -1

                                If SQL_Parametrs(i) <> "table" Then

                                    SendExeption("FIRST_MUST_BE_TABLE")

                                End If

                                If SQL_Parametrs(i) = "table" Then
                                    last_state = state
                                    state = 0

                                End If

                            Case 0

                                If ALL_TABLES.Contains(SQL_Parametrs(i)) = False Then

                                    SendExeption("TABLE_NOT_EXIST")

                                End If

                                If last_state = 6 Or last_state = 9 Then
                                    _SQL_ATACH = ""
                                    _SQL_FROM = ""
                                    _SQL_GROUP = ""
                                    _SQL_ORDER = ""
                                    _SQL_SELECT = ""
                                    _SQL_WHERE = ""
                                End If

                                CurrentTable = SQL_Parametrs(i)
                                TableOrder.Add(SQL_Parametrs(i))
                                CurrentColumns = GetColumnsFromSchema(CurrentTable)
                                If last_state = 6 Then



                                Else

                                    If _SQL_FROM = "" Then _SQL_FROM = CurrentTable Else _SQL_FROM += ", ##" & CurrentTable & "## SELECT " & CurrentTable

                                End If

                                last_state = state
                                state = 2

                            Case 2

                                Select Case SQL_Parametrs(i).ToString.ToLower

                                    Case "select"

                                        last_state = 1
                                        state = 7
                                    Case "where_and"

                                        last_state = 3
                                        state = 7

                                    Case "up"

                                        If TableOrder.Count = 0 Or TableOrder.Count = 0 Then
                                            SendExeption("CANT_GO_UP")
                                        End If

                                        CuurentAttacjSql.Add(CurrentTable)
                                        last_state = 2
                                        state = 2
                                        _SQL_FROM += "##/" & CurrentTable & "##"

                                    Case "attach"

                                        last_state = 6
                                        state = 0

                                    Case "where_or"

                                        last_state = 11
                                        state = 7

                                    Case "where"

                                        _SQL_WHERE = ""
                                        last_state = 3
                                        state = 7

                                    Case "group"

                                        state = 5

                                    Case "order"

                                        last_state = 4
                                        state = 7

                                End Select

                            Case 5

                                'Здесь надо добавить проверку на значение параметра

                                If CurrentColumns.Contains(SQL_Parametrs(i)) = True Then

                                    _SQL_GROUP += " GROUP BY " & "`" & CurrentTable & "`.`" & SQL_Parametrs(i) & "` "

                                Else

                                    SendExeption("COLUMN IN GROUP DON'T EXIST")

                                End If

                                last_state = 5
                                state = 2

                            Case 12

                                Select Case last_state
                                    Case 133

                                        'Здесь надо добавить проверку на значение параметра

                                        Dim tmpCollumns As ArrayList
                                        tmpCollumns = GetColumnsFromSchema(TableOrder(TableOrder.Count - 2))

                                        If tmpCollumns.Contains(SQL_Parametrs(i)) = True Then
                                            _SQL_WHERE += " '" & SQL_Parametrs(i) & "' "
                                        Else
                                            _SQL_WHERE += TableOrder(TableOrder.Count - 2) & ".`" & SQL_Parametrs(i) & "` "
                                        End If

                                        last_state = 3
                                        state = 2

                                    Case 3

                                        'Здесь надо добавить проверку на значение параметра

                                        If CurrentColumns.Contains(SQL_Parametrs(i)) = False Then
                                            _SQL_WHERE += " '" & SQL_Parametrs(i) & "' "
                                        Else
                                            _SQL_WHERE += " " & "`" & CurrentTable & "`.`" & SQL_Parametrs(i) & "` "
                                        End If

                                        last_state = 3
                                        state = 2

                                    Case 11

                                        'Здесь надо добавить проверку на значение параметра

                                        If CurrentColumns.Contains(SQL_Parametrs(i)) = False Then
                                            _SQL_WHERE += " '" & SQL_Parametrs(i) & "' "
                                        Else
                                            _SQL_WHERE += " " & "`" & CurrentTable & "`.`" & SQL_Parametrs(i) & "` "
                                        End If

                                        last_state = 11
                                        state = 2

                                    Case 1311

                                        'Здесь надо добавить проверку на значение параметра

                                        Dim tmpCollumns As ArrayList
                                        tmpCollumns = GetColumnsFromSchema(TableOrder(TableOrder.Count - 2))

                                        If tmpCollumns.Contains(SQL_Parametrs(i)) = True Then
                                            _SQL_WHERE += " '" & SQL_Parametrs(i) & "' "
                                        Else
                                            _SQL_WHERE += TableOrder(TableOrder.Count - 2) & ".`" & SQL_Parametrs(i) & "` "
                                        End If

                                        last_state = 11
                                        state = 2

                                End Select

                            Case 8

                                Select Case (SQL_Parametrs(i).ToString.ToLower)

                                    Case "equal"

                                        _SQL_WHERE += " = "
                                        state = 12

                                    Case "more"

                                        _SQL_WHERE += " > "
                                        state = 12

                                    Case "less"

                                        _SQL_WHERE += " < "
                                        state = 12

                                    Case "not_equal"

                                        _SQL_WHERE += " <> "
                                        state = 12

                                    Case "not_null"

                                        _SQL_WHERE += " IS NOT NULL "
                                        state = 2

                                    Case "equal_more"

                                        _SQL_WHERE += " >= "
                                        state = 12

                                    Case "equal_less"

                                        _SQL_WHERE += " <= "
                                        state = 12

                                End Select

                            Case 7

                                'Здесь пишем когда ждем поле
                                If SQL_Parametrs(i).ToString.ToLower = "from_up" Then

                                    If TableOrder.Count = 1 Then SendExeption("CANT_SELECT_UP_TABLE")

                                    If last_state = 3 Then last_state = 133
                                    If last_state = 11 Then last_state = 1311

                                    state = 7

                                    Continue For
                                End If

                                If SQL_Parametrs(i).ToString.ToLower() = "desc" Then

                                    last_state = 14

                                    Continue For

                                End If

                                Select Case last_state
                                    Case 14 ' был order desc

                                        If CurrentColumns.Contains(SQL_Parametrs(i)) = True Then

                                            If _SQL_ORDER = "" Then
                                                _SQL_ORDER = "ORDER BY `" & SQL_Parametrs(i) & "` DESC"
                                            Else
                                                _SQL_ORDER += ", " & "`" & CurrentTable & "`.`" & SQL_Parametrs(i) & "` DESC"
                                            End If

                                            state = 2

                                        Else
                                            SendExeption("COLUMN IN ORDER DON'T EXIST")

                                        End If

                                    Case 4 ' был order

                                        If CurrentColumns.Contains(SQL_Parametrs(i)) = True Then
                                            If _SQL_ORDER = "" Then
                                                _SQL_ORDER = "ORDER BY " & "`" & CurrentTable & "`.`" & SQL_Parametrs(i) & "` "
                                            Else
                                                _SQL_ORDER += ", " & "`" & CurrentTable & "`.`" & SQL_Parametrs(i) & "` "
                                            End If

                                            state = 2

                                        Else
                                            SendExeption("COLUMN IN ORDER DON'T EXIST")

                                        End If

                                    Case 133  ' был where and

                                        Dim tmpCollumns As ArrayList

                                        tmpCollumns = GetColumnsFromSchema(TableOrder(TableOrder.Count - 2))

                                        If tmpCollumns.Contains(SQL_Parametrs(i)) = True Then

                                            If _SQL_WHERE = "" Then _SQL_WHERE += " " & TableOrder(TableOrder.Count - 2) & "." & SQL_Parametrs(i) Else _SQL_WHERE += " AND " & TableOrder(TableOrder.Count - 2) & "." & SQL_Parametrs(i)

                                            last_state = 133
                                            state = 8

                                        Else
                                            SendExeption("COLUMN IN WHERE DON'T EXIST")
                                        End If


                                    Case 3  ' был where and

                                        If CurrentColumns.Contains(SQL_Parametrs(i)) = True Then

                                            If _SQL_WHERE = "" Then _SQL_WHERE += " " & CurrentTable & "." & SQL_Parametrs(i) Else _SQL_WHERE += " AND " & CurrentTable & "." & SQL_Parametrs(i)

                                            last_state = 3
                                            state = 8

                                        Else

                                            SendExeption("COLUMN IN WHERE DON'T EXIST")

                                        End If

                                    Case 1311  ' был where or

                                        Dim tmpCollumns As ArrayList

                                        tmpCollumns = GetColumnsFromSchema(TableOrder(TableOrder.Count - 2))

                                        If tmpCollumns.Contains(SQL_Parametrs(i)) = True Then

                                            If _SQL_WHERE = "" Then _SQL_WHERE += " " & TableOrder(TableOrder.Count - 2) & "." & SQL_Parametrs(i) Else _SQL_WHERE += " OR " & TableOrder(TableOrder.Count - 2) & "." & SQL_Parametrs(i)

                                            last_state = 1311
                                            state = 8

                                        Else
                                            SendExeption("COLUMN IN WHERE DON'T EXIST")
                                        End If
                                    Case 11  ' был where or

                                        If CurrentColumns.Contains(SQL_Parametrs(i)) = True Then

                                            If _SQL_WHERE = "" Then _SQL_WHERE += " " & CurrentTable & "." & SQL_Parametrs(i) Else _SQL_WHERE += " OR " & CurrentTable & "." & SQL_Parametrs(i)

                                            last_state = 11
                                            state = 8

                                        Else
                                            SendExeption("COLUMN IN WHERE DON'T EXIST")
                                        End If


                                    Case 1 'Был select

                                        If CurrentColumns.Contains(SQL_Parametrs(i)) = True Or SQL_Parametrs(i).ToString.ToLower = "all" Then

                                            last_state = state
                                            state = 2

                                            If SQL_Parametrs(i).ToString.ToLower = "all" Then
                                                If _SQL_SELECT = "" Then _SQL_SELECT = "*" Else _SQL_SELECT += "," & "*"
                                            Else
                                                If _SQL_SELECT = "" Then _SQL_SELECT = SQL_Parametrs(i) Else _SQL_SELECT += "," & SQL_Parametrs(i)
                                            End If

                                        Else
                                            SendExeption("COLUMN IN SELECT DON'T EXIST")
                                        End If
                                End Select

                        End Select
                    Next

                    If _SQL_WHERE <> "" Then _SQL_WHERE = " WHERE " & _SQL_WHERE

                    TotalSQL = String.Format("SELECT {0} FROM {1}{2} {3} {4}", _SQL_SELECT, _SQL_FROM, _SQL_WHERE, _SQL_GROUP, _SQL_ORDER)

                    Dim js As New JavaScriptSerializer

                    Dim obj As Object = CreateObjectFromSQL(TotalSQL)
                    app.Response.Write(js.Serialize(obj))
                    app.Response.End()

                End If

                Using con As New OdbcConnection(ConfigurationManager.AppSettings("WRADA_ConnectionString")), com As New OdbcCommand("SELECT " & parms("select").Replace("all", "*") & " FROM " & _tables.Replace("'", "") & ";")

                    con.Open()
                    com.Connection = con

                    Using reader As OdbcDataReader = com.ExecuteReader

                        Dim result As New ArrayList

                        While reader.Read

                            Dim obj As New Dictionary(Of String, Object)

                            For i As Integer = 0 To reader.FieldCount - 1

                                If reader.IsDBNull(i) = True Then
                                    obj.Add(reader.GetName(i), Nothing)
                                Else
                                    obj.Add(reader.GetName(i), reader.GetValue(i))
                                End If

                            Next

                            result.Add(obj)

                        End While

                        Dim js As New JavaScriptSerializer

                        app.Response.Write(js.Serialize(result))

                    End Using
                End Using
            End If

            app.Context.Response.End()

        End Sub

        Public Sub CreateAssemblies(app As HttpApplication, Optional UseDB As Boolean = True, Optional session As WSession = Nothing)
            app.Response.Write("<div style='padding:5px; color:white; border:1px solid #eeeeee; background:#1C90FA'><h3>Avialible classes</h3><h2>WID: " & session.WID & "</h2></div>")
            For Each a As Type In Assembly.GetExecutingAssembly.GetTypes().OrderBy(Function(x) x.Name)

                If {"_default", "MyProject", "WRADA_PUSH_SERVICE", "WRADA_HTTP_Module", "ThreadSafeObjectProvider`1", "InternalXmlHelper", "RemoveNamespaceAttributesClosure", "MyWebExtension", "Resources", "MySettings", "MySettingsProperty"}.Contains(a.Name) = True Then Continue For
                If (a.Name.Contains("$") = True) Or InStr(a.Name.ToLower, "wrad") = 0 Then Continue For

                app.Response.Write("<div style='padding:5px; border:1px solid #eeeeee; background:#77BEFF'>Class name: " & a.Name & "</div>")

                For Each va As FieldInfo In a.GetFields.OrderBy(Function(x) x.Name)

                    app.Response.Write(String.Format("<b>Variable {0}</b> as {1}<br />", va.Name, va.FieldType.Name))

                    Dim porttmp As String = ""

                    If app.Request.Url.Port <> 80 Then porttmp = String.Format(":{0}", app.Request.Url.Port)

                    app.Response.Write(String.Format("Example to use with GET query: http://{0}{1}/wrada/method/var/class/{2}/variable/{3}/", app.Request.Url.Host, porttmp, a.Name, va.Name))
                    app.Response.Write("<br /><br />")

                Next

                For Each sa As MethodInfo In a.GetMethods.OrderBy(Function(x) x.Name)

                    If sa.Name = "ToString" Or sa.Name = "Equals" Or sa.Name = "GetHashCode" Or sa.Name = "GetType" Then Continue For

                    If sa.GetCustomAttributes(False).Count(Function(x) x.ToString().IndexOf("HideData") >= 0) > 0 Then
                        Continue For
                    End If

                    Dim col1 As String = "#DEECF3"
                    Dim col2 As String = "#BBD9FF"

                    If sa.GetCustomAttributes(False).Count(Function(x) x.ToString().IndexOf("FunctionState") >= 0) > 0 Then
                        Select Case CType(sa.GetCustomAttributes(False).FirstOrDefault(Function(x) x.ToString().IndexOf("FunctionState") >= 0), FunctionState).State
                            Case FunctionState.FState.Bug
                                col1 = "#EEC1C1"
                                col2 = "#FD0000"
                            Case FunctionState.FState.Stable
                                col1 = "#DEECF3"
                                col2 = "#BBD9FF"
                            Case FunctionState.FState.Test
                                col1 = "#F0E281"
                                col2 = "#EEEE00"
                        End Select
                    End If


                    app.Response.Write("<div class='md50' style='margin:1%; width:46%; background:" & col1 & "; padding:1%; float:left;'><div style='background:" & col2 & "'><table style='width:100%;'><tr><td>Function <b>" & sa.Name & "</b> as <b>  " & sa.ReturnType.Name & "</b></td><td style='text-align:right;'>" & (IIf(sa.GetCustomAttributes(False).Count(Function(x) x.ToString().IndexOf("NeedAuth") >= 0) > 0, "<span style='text-decoration:underline'>Need WRAD_AUTH</span><br />", "")) & "</td></tr></table></div>")

                    If sa.GetCustomAttributes(False).Count(Function(x) x.ToString().IndexOf("Description") >= 0) > 0 Then
                        app.Response.Write("<br /><span style=''>Description: " & CType(sa.GetCustomAttributes(False).FirstOrDefault(Function(x) x.ToString().IndexOf("Description") >= 0), Description).Description & "</span><br /><br />")
                    End If

                    If sa.GetCustomAttributes(False).Count(Function(x) x.ToString().IndexOf("PostParametrInfo") >= 0) > 0 Then
                        app.Response.Write("<span style=''>Post Parametrs: " & CType(sa.GetCustomAttributes(False).FirstOrDefault(Function(x) x.ToString().IndexOf("PostParametrInfo") >= 0), PostParametrInfo).Parametrs.Replace(",", ", ") & "</span><br />")
                    End If


                    If sa.GetCustomAttributes(False).Count(Function(x) x.ToString().IndexOf("PostParametrData") >= 0) > 0 Then

                        app.Response.Write("Post parametrs:<br />")
                        app.Response.Write("<table style='padding-left:30px; width:300px;'>")

                        Dim pd As PostParametrData = CType(sa.GetCustomAttributes(False).FirstOrDefault(Function(x) x.ToString().IndexOf("PostParametrData") >= 0), PostParametrData)

                        For i As Integer = 0 To pd.Parametrs.Count - 1

                            app.Response.Write("<tr><td>")
                            app.Response.Write(pd.Parametrs.Keys(i))
                            app.Response.Write("</td><td>")
                            app.Response.Write(pd.Parametrs(pd.Parametrs.Keys(i)))
                            app.Response.Write("</td></tr>")

                        Next

                        app.Response.Write("</table><br />")

                    End If

                    If sa.GetParameters.Length <> 0 Then app.Response.Write("Parametrs count: " & sa.GetParameters.Length & "<br />")

                    Dim tmp As String = ""

                    For Each p As ParameterInfo In sa.GetParameters

                        app.Response.Write(String.Format("{0} ({1})<br />", p.Name, p.ParameterType.Name))
                        tmp += String.Format("{0}/{{{1}}}/", p.Name, p.ParameterType.Name)

                    Next

                    Dim porttmp As String = ""

                    If app.Request.Url.Port <> 80 Then porttmp = ":" & app.Request.Url.Port


                    If sa.GetCustomAttributes(False).Count(Function(x) x.ToString().IndexOf("PostParametrData") >= 0) > 0 Then

                        app.Response.Write("Testing form:<br />")
                        app.Response.Write("<form action='http://" & app.Request.Url.Host & porttmp & "/wrada/method/call/class/" & a.Name & "/func/" & sa.Name & "/" & tmp & "' method='post'><table style='padding-left:30px;'>")

                        Dim pd As PostParametrData = CType(sa.GetCustomAttributes(False).FirstOrDefault(Function(x) x.ToString().IndexOf("PostParametrData") >= 0), PostParametrData)

                        For i As Integer = 0 To pd.Parametrs.Count - 1

                            app.Response.Write("<tr><td style='padding-right:30px;'>")
                            app.Response.Write(pd.Parametrs.Keys(i))
                            app.Response.Write(" (")
                            app.Response.Write(pd.Parametrs(pd.Parametrs.Keys(i)))

                            app.Response.Write(String.Format(")</td><td><input type='text' name='{0}' style='width:300px;' />", pd.Parametrs.Keys(i)))

                            app.Response.Write("</td></tr>")

                        Next
                        app.Response.Write("<tr><td colspan=2 style='text-align:right;'><input type='submit' value='Send'/></td></tr>")
                        app.Response.Write("</table></form><br />")

                    End If

                    app.Response.Write(String.Format("URL: <a href='http://{0}{1}/wrada/method/call/class/{2}/func/{3}/{4}'>http://{0}{1}/wrada/method/call/class/{2}/func/{3}/{4}</a><br />", app.Request.Url.Host, porttmp, a.Name, sa.Name, tmp))

                    app.Response.Write("</div>")

                Next

            Next
            If UseDB = False Then Exit Sub

            app.Response.Write("<div style='padding:5px; border:1px solid #eeeeee; background:#77BEFF'><h3>Доступные таблицы</h3></div>")


            Using con As New OdbcConnection(ConfigurationManager.AppSettings("WRADA_ConnectionString")), com As New OdbcCommand("SELECT `information_schema`.`TABLES`.`TABLE_NAME` ,TABLE_COMMENT FROM `information_schema`.`TABLES`  WHERE `information_schema`.`TABLES`.`TABLE_SCHEMA`='" & "###" & "';")

                con.Open()
                com.Connection = con
                com.CommandText = com.CommandText.Replace("###", con.Database)

                Dim porttmp As String = ""

                If app.Request.Url.Port <> 80 Then porttmp = String.Format(":{0}", app.Request.Url.Port)

                Using reader As OdbcDataReader = com.ExecuteReader

                    While reader.Read

                        app.Response.Write(String.Format("<div style='padding:5px; border:1px solid #eeeeee; background:#77BEFF'><b>{0}</b></div>", reader.GetString(0)))

                        app.Response.Write(String.Format("Описание таблицы: {0}<br />", reader.GetString(1)))
                        app.Response.Write(String.Format("Получить схему таблицы: http://{0}{1}/wrada/method/data/table/{2}/select/schema/" & "<br /><br />", app.Request.Url.Host, porttmp, reader.GetString(0)))

                    End While

                End Using

                con.Close()

            End Using

        End Sub

        Function CreateObjectFromSQL(sql As String) As Object

            Dim result As New ArrayList

            Using con As New OdbcConnection(ConfigurationManager.AppSettings("WRADA_ConnectionString")), com As New OdbcCommand(sql)

                con.Open()

                com.Connection = con

                Using reader As OdbcDataReader = com.ExecuteReader
                    While reader.Read
                        Dim obj As New Dictionary(Of String, Object)
                        For i As Integer = 0 To reader.FieldCount - 1

                            If reader.IsDBNull(i) = True Then
                                obj.Add(reader.GetName(i), "")
                            Else
                                obj.Add(reader.GetName(i), reader.GetValue(i))
                            End If
                        Next

                        result.Add(obj)

                    End While
                End Using
            End Using

            Return IIf(result.Count = 0, Nothing, IIf(result.Count <> 1, result, result(0)))

            'Возможно лучше поставить возврат масивом всегда, нужно поинтересоваться у прогеров моб.
            'Return result

        End Function

        Function GetTablesFromSchema() As ArrayList

            Dim result As New ArrayList

            Using con As New OdbcConnection(ConfigurationManager.AppSettings("WRADA_ConnectionString")), com As New OdbcCommand("SELECT TABLE_NAME FROM `information_schema`.`TABLES` Where TABLE_SCHEMA = '###'")

                con.Open()

                com.Connection = con

                com.CommandText = com.CommandText.Replace("###", con.Database)

                Using reader As OdbcDataReader = com.ExecuteReader

                    While reader.Read
                        result.Add(reader.GetString(0))
                    End While
                End Using
            End Using

            Return result

        End Function

        Function GetColumnsFromSchema(table As String) As ArrayList

            Dim result As New ArrayList

            Using con As New OdbcConnection(ConfigurationManager.AppSettings("WRADA_ConnectionString")), com As New OdbcCommand("SELECT COLUMN_NAME FROM `information_schema`.`COLUMNS` Where TABLE_NAME = '" & table & "'")
                con.Open()
                com.Connection = con
                Using reader As OdbcDataReader = com.ExecuteReader

                    While reader.Read
                        result.Add(reader.GetString(0))
                    End While
                End Using
            End Using

            Return result

        End Function
        Sub SendExeption(str As String)

            HttpContext.Current.Response.Write("{'error' : '" & str & "'}")

            HttpContext.Current.Response.End()

        End Sub

        Public Class MyHttpHandler

            Implements IHttpHandler, IReadOnlySessionState


            ReadOnly OriginalHandler As IHttpHandler
            Sub New(originalHandler2 As IHttpHandler)

                OriginalHandler = originalHandler2

            End Sub

            ReadOnly Property IsReusable As Boolean
                Get
                    Return False
                End Get
            End Property


            Public ReadOnly Property IsReusable1 As Boolean Implements IHttpHandler.IsReusable
                Get
                    Return False
                End Get
            End Property

            Public Sub ProcessRequest(context As HttpContext) Implements IHttpHandler.ProcessRequest

            End Sub
        End Class

    End Class
End Namespace